-- Examples for queries that exercise different SQL objects implemented by this assembly

-----------------------------------------------------------------------------------------
-- Stored procedure
-----------------------------------------------------------------------------------------
-- exec StoredProcedureName


-----------------------------------------------------------------------------------------
-- User defined function
-----------------------------------------------------------------------------------------
-- select dbo.FunctionName()


-----------------------------------------------------------------------------------------
-- User defined type
-----------------------------------------------------------------------------------------
-- CREATE TABLE test_table (col1 UserType)
-- go
--
-- INSERT INTO test_table VALUES (convert(uri, 'Instantiation String 1'))
-- INSERT INTO test_table VALUES (convert(uri, 'Instantiation String 2'))
-- INSERT INTO test_table VALUES (convert(uri, 'Instantiation String 3'))
--
-- select col1::method1() from test_table



-----------------------------------------------------------------------------------------
-- User defined type
-----------------------------------------------------------------------------------------
-- select dbo.AggregateName(Column1) from Table1
--USE master 
--GO  
-- 
--CREATE ASYMMETRIC KEY SQLCLRTestKey FROM ASSEMBLY SQLServerUDTs--'C:\Documents and Settings\Michael\My Documents\Visual Studio 2005\Projects\moDb\moDb_prototype\bin\Debug\moDb_prototype.dll'   
--CREATE LOGIN SQLCLRTestLogin FROM ASYMMETRIC KEY SQLCLRTestKey   
--GRANT EXTERNAL ACCESS ASSEMBLY TO SQLCLRTestLogin 
--GO 
--
--ALTER ASSEMBLY SQLServerUDTs WITH PERMISSION_SET = EXTERNAL_ACCESS
--ALTER ASSEMBLY SQLServerUDTs FROM 'C:\Documents and Settings\Michael\My Documents\Visual Studio 2005\Projects\moDb\moDb_prototype\bin\Debug\moDb_prototype.dll' WITH UNCHECKED DATA 
--
--DECLARE @assemblyPath VARCHAR(MAX) 
----Zainul - Change this to your assembly path :)
--SET @assemblyPath = 'C:\Documents and Settings\Michael\My Documents\Visual Studio 2005\Projects\moDb\moDb_prototype\bin\Debug\moDb_prototype.dll'
--
----DROP Type Person
--
----DROP Assembly SQLServerUDTs
--
--CREATE ASSEMBLY SQLServerUDTs FROM @assemblyPath
--WITH PERMISSION_SET = EXTERNAL_ACCESS
--
--CREATE TYPE Person EXTERNAL NAME SQLServerUDTs.[moDb_prototype.Person]
--DROP TYPE Person

CREATE TABLE Person
(Id int IDENTITY(1,1) PRIMARY KEY,
Person Person NULL)

DECLARE @Begin DATETIME, @End DATETIME, @Count INT
SET @Count = 0 
SET @Begin = GETDATE() 
WHILE (@Count < 4)
BEGIN
	SET @Count = @Count + 1 
	INSERT INTO person (Person) VALUES ('ID=1|Age=25|Name=Michael|DateOfBirth = 1981/01/18')
	INSERT INTO person (Person) VALUES ('ID=2|Age=26|Name=Peter|DateOfBirth = 1985/12/01')
	INSERT INTO person (Person) VALUES ('ID=3|Age=27|Name=John|DateOfBirth = 1979/07/12')
END
SET @End = GETDATE() 
PRINT @Begin
PRINT @End
PRINT DATEDIFF(ms, @Begin, @End)

--DELETE FROM Person
	
--SELECT Person.Person.ToString() FROM Person
--SELECT Person.Person.Age FROM Person

--SELECT * FROM PERSON

DELETE FROM Person

DROP TABLE Person 

--Cleanup in case of error
--DROP TABLE Person 
--DROP TYPE Person
--DROP ASSEMBLY moDB_Prototype